<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2019/1/2
 * Time: 14:12
 */

namespace app\console\controller;
use think\Db;
use think\Request;
use think\Session;
use app\console\model\RefundOrderAudit;

class Finance
{
    /**
     * 财务管理
     */
    public function index($pa = 10)
    {

        $param = Request::instance()->param();
        $level = \session('role_id');//当前登录的管理员界别 5 7 8 9 10
        $page = Request::instance()->param('page',10);
        $where = [];
        $auding_status = Session::get('auding_status');
        if($auding_status == 1){
            $where['shop_id'] = ['eq',\session('shop_id')];
        }


        if (!empty($param['status'])){
            if($param['status'] == 2){
                $where['o.status'] = ['in', '2,6'];
            }else{
                $where['o.status'] = ['eq', $param['status']];
            }
        }
        if (!empty($param['shop_id'])) $where['o.shop_id'] = ['eq', $param['shop_id']];
        if (!empty($param['order_type'])) $where['o.order_type'] = ['eq', $param['order_type']];
        if (!empty($param['title'])){
            $order_goods  = Db::name('order_goods')->where('title','like', '%'.$param['title'].'%')->find();
            $gift = Db::name('order o')->join('gift g','o.gift_id = g.id')->where('g.title','like','%'.$param['title'].'%')->find();
            if(empty($order_goods) && empty($gift)){
                $where['m.username|o.shop_order_number'] = ['like', '%'.$param['title'].'%'];
            }
            if(!empty($order_goods)){
                $where['o.order_type'] = ['eq', 1];
                $where['og.title|o.username'] = ['like', '%'.$param['title'].'%'];
            }
            if(!empty($gift)){
                $where['o.order_type'] = ['eq', 3];
               // $where['g.title|o.username'] = ['like', '%'.$param['title'].'%'];
            }

        }


        $end_time = time();
        if (!empty($param['interval_timer'])) {
            $interval_timer = explode('/', $param['interval_timer']);
            $end_time = strtotime($interval_timer[1]);
            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];
        }

        $total_price = Db::name('order_goods og')
            ->join('order o','o.id = og.order_id','LEFT')
            ->join('member m', 'o.member_id = m.id','LEFT')
            ->join('shop s', 'o.shop_id = s.id','LEFT')
            ->field('o.*,m.username,s.shop_title')
            ->where($where)->sum('amount');
        $d_price = 0;
        $c_price = 0;
        $in_array = [];

        $data = \app\console\model\OrderGoods::alias('og')
            ->join('order o','o.id = og.order_id','LEFT')
            ->join('member m', 'o.member_id = m.id','LEFT')
            ->join('shop s', 'o.shop_id = s.id','LEFT')
            ->field('o.*,m.username,s.shop_title')
            ->field('og.title,o.gift_id,o.order_number,o.gift_id,o.shop_id,DATE_FORMAT(o.create_time,"%Y/%m/%d") create_time,o.id,o.order_type,o.single_parent_id,o.single_id,og.goods_id,o.amount,o.shop_order_number,m.username,o.status,o.refund_create_time,s.shop_title,o.shipping,o.is_read,o.order_number')
            ->where($where)
            ->fetchSql(false)
            ->order('o.id','desc')
            ->paginate($pa, false, ['query' => $param])->each(function($item,$key) use($level,$end_time,&$d_price,&$c_price,&$in_array){


            });

        if(isset($param['export'])){
            $header[0] = [
                'pzrq' => date('Y/n/j',$end_time),
                'kjnd' => date('Y',$end_time),
                'kjqj' => date('n',$end_time),
                'pzz' => '记',
                'pzh' => '',
                'subject_code' => '1001',
                'subject_name' => '',
                'bbdm' => 'RMB',
                'bbmc' => '人民币',
                'amount' => $total_price,
                'd' => $d_price,
                'c' => $c_price,
                'zd' => '',
                'sh' => '',
                'hz' => 'NONE',
                'cn' => 'NONE',
                'jb' => '',
                'jsfs' => '*',
                'jsh' => '',
                'pzzy' => '现金',
                'sl' => '0',
                'sldw' => '*',
                'dj' => '',
                'ckxx' => '',
                'ywrq' => date('Y/n/j',$end_time),
                'wlyybh' => '',
                'ffs' => '0',
                'xh' => '1',
                'xtmk' => '',
                'ywms' => '',
                'hl' => '1',
                'flxh' => 0,
                'hsxm' => '',
                'gz' => '0',
                'jzpz' => '',
                'xjll' => '',
                'order_number' => '',
                'others' => ''
            ];
            $array = array_merge($header,$data->toArray()['data']);
            $this->push($array);
        }


        return view('', ['data' => $data,
            'shop_list' => [],
            'total_count' => 0,
            'total_price' => 0,
        ]);
    }

    public function daochu()
    {

        $param = Request::instance()->param();
        $level = \session('role_id');//当前登录的管理员界别 5 7 8 9 10
        $page = Request::instance()->param('page',10);
        $where = [];
        $auding_status = Session::get('auding_status');
        if($auding_status == 1){
            $where['shop_id'] = ['eq',\session('shop_id')];
        }


        if (!empty($param['status'])) $where['o.status'] = ['eq', $param['status']];
        if (!empty($param['shop_id'])) $where['o.shop_id'] = ['eq', $param['shop_id']];
        if (!empty($param['title'])) $where['m.username|o.shop_order_number|o.order_number'] = ['eq', $param['title']];

        $end_time = time();
        if (!empty($param['interval_timer'])) {
            $interval_timer = explode('/', $param['interval_timer']);
            $end_time = strtotime($interval_timer[1]);
            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];
        }


        $data = \app\console\model\Order::alias('o')
            ->join('order_goods og','o.id = og.order_id','left')
            ->join('member m', 'o.member_id = m.id','left')
            ->join('shop s', 'o.shop_id = s.id','left')
            ->field('o.order_number,o.gift_id,o.shop_id,DATE_FORMAT(o.create_time,"%Y/%m/%d") create_time,o.id,o.order_type,o.single_parent_id,o.single_id,og.goods_id,o.amount,o.shop_order_number,m.username,o.status,o.refund_create_time,s.shop_title,o.shipping,o.is_read,o.order_number')
            ->where($where)
            ->fetchSql(false)
            ->order('o.id','desc')
            ->paginate(10000, false, ['query' => $param])->each(function($item,$key) use($level,$end_time){
                if($item['status'] == 12 || $item['status'] == 10 || $item['status'] == 11){
                    $item->refund_create_time = Db::name('order_refund')->where('order_id',$item['id'])->value('create_time');
                }
                $item->year = explode('/',$item->create_time)[0];
                $item->month = explode('/',$item->create_time)[1];
                $item->pzrq = date('Y/m/d',$end_time);//凭证日期
                $item->kjnd = date('Y',$end_time);//会计年度
                $item->kjqj = date('m',$end_time);//会计期间
                $item->pzz = '记';//凭证字
                $item->pzh = $key;//凭证号
                $item->d = '';
                $item->c = '';
                $item->pzzy = '';
                switch ($item->order_type){
                    case 1;
                        $dc = Db::name('shop_goods')->where('id',$item->goods_id)->value('dc');
                        $title = Db::name('shop_goods')->where('id',$item->goods_id)->value('title');
                        $item->pzzy = $title;
                        if($dc == 1){
                            $item->d = $item->amount;
                        }else{
                            $item->c = $item->amount;
                        }
                        $item->subject_code = Db::name('shop_goods')->where('id',$item->goods_id)->value('code');
                        break;
                    case 2;
                        $dc = Db::name('single')->where('id',$item->single_id)->value('dc');
                        $title = Db::name('single')->where('id',$item->single_id)->value('cat_name');
                        $item->pzzy = $title;
                        if($dc == 1){
                            $item->d = $item->amount;
                        }else{
                            $item->c = $item->amount;
                        }
                        $item->subject_code = Db::name('single')->where('id',$item->single_parent_id)->value('code').'.'.Db::name('single')->where('id',$item->single_id)->value('code');
                        break;
                    case 3;
                        $dc = Db::name('gift')->where('id',$item->gift_id)->value('dc');
                        $title = Db::name('gift')->where('id',$item->gift_id)->value('title');
                        $item->pzzy = $title;
                        if($dc == 1){
                            $item->d = $item->amount;
                        }else{
                            $item->c = $item->amount;
                        }
                        $item->subject_code = Db::name('gift')->where('id',$item->gift_id)->value('code');
                        break;
                }
                $item->subject_name = '';//科目名称
                $item->bbdm = 'RMB';//币别代码
                $item->bbmc = '人民币';//币别代码
                $item->amount = $item->amount;//原币金额
                $item->zd = '';//制单
                $item->sh = '';//审核
                $item->hz = 'NONE';//核准
                $item->cn = 'NONE';//出纳
                $item->jb = '';//经办
                $item->jsfs = '*';//结算方式
                $item->jsh = '';//结算号
                $item->sl = '0';//数量
                $item->sldw = '*';//数量单位
                $item->dj = '';//单价
                $item->ckxx = '';//参考信息
                $item->ywrq = '';//业务日期  ....
                $item->wlyybh = '';//往来业务编号
                $item->ffs = 0;//附件数
                $item->xh = 1 ;//序号
                $item->xtmk = '';//系统模块
                $item->ywms = '';//业务描述
                $item->hl = 1;//汇率
                $item->flxh = '';//分录序号
                $item->hsxm = '';//合算项目
                $item->gz = 0;//过账
                $item->jzpz = '';//机制凭证
                $item->xjll = '';//现金流量
                $item->order_number = '';//现金流量

            });

        if(isset($param['export'])){
            $this->push($data);
        }

    }

    public function push($data, $name = '财务数据导出')
    {

        set_time_limit(0);

        ini_set('memory_limit', '1024M');

        error_reporting(E_ALL);

        date_default_timezone_set('Asia/Shanghai');



        import('phpoffice.phpexcel.Classes.PHPExcel', EXTEND_PATH, '.php');

        $objPHPExcel = new \PHPExcel();



        /*以下是一些设置 ，什么作者  标题啊之类的*/

        $objPHPExcel->getProperties()->setCreator("管理员")

            ->setLastModifiedBy("管理员")

            ->setTitle("电话本导出")

            ->setSubject("数据EXCEL导出")

            ->setDescription("备份数据")

            ->setKeywords("excel")

            ->setCategory("result file");

        $objPHPExcel->setActiveSheetIndex(0)

            //Excel的第A列，shop_name是你查出数组的键值，下面以此类推
            ->setCellValue('A1', '凭证日期')
            ->setCellValue('B1', '会计年度')
            ->setCellValue('C1', '会计期间')
            ->setCellValue('D1', '凭证字')
            ->setCellValue('E1', '凭证号')
            ->setCellValue('F1', '科目代码')
            ->setCellValue('G1', '科目名称')
            ->setCellValue('H1', '币别代码')
            ->setCellValue('I1', '币别名称')
            ->setCellValue('J1', '原币金额')
            ->setCellValue('K1', '借方')
            ->setCellValue('L1', '贷方')
            ->setCellValue('M1', '制单')
            ->setCellValue('N1', '审核')
            ->setCellValue('O1', '核准')
            ->setCellValue('P1', '出纳')
            ->setCellValue('Q1', '经办')
            ->setCellValue('R1', '结算方式')
            ->setCellValue('S1', '结算号')
            ->setCellValue('T1', '凭证摘要')
            ->setCellValue('U1', '数量')
            ->setCellValue('V1', '数量单位')
            ->setCellValue('W1', '单价')
            ->setCellValue('X1', '参考信息')
            ->setCellValue('Y1', '业务日期')
            ->setCellValue('Z1', '往来业务编号')
            ->setCellValue('AA1', '附件数')
            ->setCellValue('AB1', '序号')
            ->setCellValue('AC1', '系统模块')
            ->setCellValue('AD1', '业务描述')
            ->setCellValue('AE1', '汇率')
            ->setCellValue('AF1', '分录序号')
            ->setCellValue('AG1', '核算项目')
            ->setCellValue('AH1', '过账')
            ->setCellValue('AI1', '机制凭证')
            ->setCellValue('AJ1', '现金流量')
            ->setCellValue('AK1', '订单编号')
            ->setCellValue('AL1', '商品其他信息')
        ;

        $sheet1 = [];


        for ($x = 0; $x < count($sheet1); $x++) {

            $objPHPExcel->getActiveSheet()->getStyle($sheet1[$x] . '1')->applyFromArray(

                array(

                    'font'      => array(

                        'bold' => true

                    ),

                    'alignment' => array(

                        'horizontal' =>

                            \PHPExcel_Style_Alignment::HORIZONTAL_CENTER

                    )

                )

            );

        }



        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/



        $n = 0;

        foreach ($data as $v) {

            $num = $n + 2;
            $objPHPExcel->setActiveSheetIndex(0)

                //Excel的第A列，shop_name是你查出数组的键值，下面以此类推
                ->setCellValue('A' . $num, $v['pzrq'])
                ->setCellValue('B' . $num, $v['kjnd'])
                ->setCellValue('C' . $num, $v['kjqj'])
                ->setCellValue('D' . $num, $v['pzz'])
                ->setCellValue('E' . $num, $v['pzh'])
                ->setCellValue('F' . $num, $v['subject_code'])
                ->setCellValue('G' . $num, $v['subject_name'])
                ->setCellValue('H' . $num, $v['bbdm'])
                ->setCellValue('I' . $num, $v['bbmc'])
                ->setCellValue('J' . $num, $v['amount'])
                ->setCellValue('K' . $num, $v['d'])
                ->setCellValue('L' . $num, $v['c'])
                ->setCellValue('M' . $num, $v['zd'])
                ->setCellValue('N' . $num, $v['sh'])
                ->setCellValue('O' . $num, $v['hz'])
                ->setCellValue('P' . $num, $v['cn'])
                ->setCellValue('Q' . $num, $v['jb'])
                ->setCellValue('R' . $num, $v['jsfs'])
                ->setCellValue('S' . $num, $v['jsh'])
                ->setCellValue('T' . $num, $v['pzzy'])
                ->setCellValue('U' . $num, $v['sl'])
                ->setCellValue('V' . $num, $v['sldw'])
                ->setCellValue('W' . $num, $v['dj'])
                ->setCellValue('X' . $num, $v['ckxx'])
                ->setCellValue('Y' . $num, $v['ywrq'])
                ->setCellValue('Z' . $num, $v['wlyybh'])
                ->setCellValue('AA' . $num, $v['ffs'])
                ->setCellValue('AB' . $num, $v['xh'])
                ->setCellValue('AC' . $num, $v['xtmk'])
                ->setCellValue('AD' . $num, $v['ywms'])
                ->setCellValue('AE' . $num, $v['hl'])
                ->setCellValue('AF' . $num, $v['flxh'])
                ->setCellValue('AG' . $num, $v['hsxm'])
                ->setCellValue('AH' . $num, $v['gz'])
                ->setCellValue('AI' . $num, $v['jzpz'])
                ->setCellValue('AJ' . $num, $v['xjll'])
                ->setCellValue('AK' . $num, $v['order_number'])
                ->setCellValue('AL' . $num, $v['others'])

            ;



            $n++;

        }



        $objPHPExcel->getActiveSheet(0)->setTitle('财务信息导出');

        $objPHPExcel->setActiveSheetIndex(0);



        ob_end_clean();

        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment;charset=utf-8;filename="' . $name . '.xls"');

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

        $objWriter->save('php://output');
        exit;
    }
}